package ipt.dao.alu06;

import ipt.enumeracion.Rol;
import ipt.exp.AlumnoException;
import ipt.vo.Alumno;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;


public class MigracionTXT {
	
	private static Connection connection = null;
	private static PreparedStatement ptmt = null;
	
	private final static String TABLE_NAME = "ALUMNOS";
	// Columnas
	private final static String COL_ID = "id";
	private final static String COL_DNI = "dni";
	private final static String COL_NOMBRE = "nombre";
	private final static String COL_APELLIDO1 = "apellido1";
	private final static String COL_APELLIDO2 = "apellido2";
	private final static String COL_EDAD = "edad";
	private final static String COL_ROL = "rol";
	private final static String COL_EMAIL = "email";
	private final static String COL_ID_CURSO = "id_curso";
	//error codes
	private final static String ERR_CODE_DUPLICATE_KEY = "S1000";
	
	
	
	public static void migrarTXT() throws SQLException, AlumnoException {
		AlumnoTXTDAO dao = new AlumnoTXTDAO();
		AlumnoJDBCDAO dao2 = new AlumnoJDBCDAO();
		String queryString = "";
		String dni = "";
		int i = 1;
		
		try {
			connection = dao2.getConnection();
			connection.setAutoCommit(false);
			List<Alumno> lista = new ArrayList<Alumno>();
			lista = dao.getAlumnos();
			
			
			Iterator<Alumno> it = lista.iterator();
			
			while (it.hasNext()) {
				Alumno alu = it.next();
				dni = alu.getDni();
				queryString = "INSERT INTO " + TABLE_NAME + 
						" (" + COL_NOMBRE + ", " + COL_APELLIDO1 + ", " + COL_APELLIDO2 + ", " + 
						COL_EDAD + ", " + COL_EMAIL + ", " + COL_DNI + ", " + COL_ROL + ", "  + COL_ID_CURSO + ") VALUES (?,?,?,?,?,?,?,?)";
				
				ptmt = connection.prepareStatement(queryString);
				
				ptmt.setString(1, alu.getNombre());
				ptmt.setString(2, alu.getApellido1());
				ptmt.setString(3, alu.getApellido2());
				ptmt.setInt(4, alu.getEdad());
				ptmt.setString(5, alu.getEmail());
				ptmt.setString(6, alu.getDni());
				ptmt.setInt(8, alu.getId_curso());
				
				if (alu.getRol() == Rol.TRABAJADOR){
					ptmt.setInt(7, 0);
				}else if (alu.getRol() == Rol.JEFE){
					ptmt.setInt(7, 1);
				}else if (alu.getRol() == Rol.CLIENTE){
					ptmt.setInt(7, 2);
				}
				
				System.out.println(i);
				
				ptmt.executeUpdate();
				ptmt.close();
				i++;
			}
			
			connection.commit();
		} catch (SQLException e) {
			connection.rollback();
			System.out.println("--- SQLException ---");
			while (e != null) {
				if(e.getSQLState() == ERR_CODE_DUPLICATE_KEY){
					throw new AlumnoException("La clave (" + dni + ") ha sido duplicada para la tabla " + TABLE_NAME); 
				}else{
					System.out.println("\nMessage: " + e.getMessage());
					System.out.println("\nSQLState: " + e.getSQLState());
					System.out.println("\nErrorCode: " + e.getErrorCode());
					e = e.getNextException();
					System.out.println("");
				}
			}
		}catch (Exception e) {
			connection.rollback();
			e.printStackTrace();
		}finally{
			try {
				if (ptmt != null){
					ptmt.close();
				}
				if (connection != null){
					connection.close();
				}
			} catch (SQLException e) {
				System.out.println("--- SQLException ---\n");
				while (e != null) {
					System.out.println("\nMessage: " + e.getMessage());
					System.out.println("\nSQLState: " + e.getSQLState());
					System.out.println("\nErrorCode: " + e.getErrorCode());
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
